using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace CloudApiServer
{
    /// <summary>
    /// Mysql帮助类
    /// </summary>
    public static class MySqlHelper
    {
        /// <summary>
        /// 执行为非查询语句 插入、更新、删除等
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">SQL 查询语句，通常是 INSERT、UPDATE 或 DELETE 语句</param>
        /// <param name="parameters">用于传递 SQL 查询语句中的参数，避免 SQL 注入攻击</param>
        /// <param name="transaction">可选的事务对象，用于控制多个操作的事务一致性</param>
        /// <returns>收到影响的行数</returns>
        /// <exception cref="ArgumentNullException">mysql执行异常</exception>
        public static async Task<int> ExecuteNonQueryAsync(
            string connectionString, 
            string query, 
            IEnumerable<MySqlParameter> parameters = null,
            MySqlTransaction transaction = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            bool shouldDisposeConnection = false;
            MySqlConnection connection = transaction?.Connection;

            try
            {
                // 如果没有连接或连接未打开，则新建或打开
                if (connection == null)
                {
                    connection = new MySqlConnection(connectionString);
                    await connection.OpenAsync();
                    shouldDisposeConnection = true;
                }
                else if (connection.State != ConnectionState.Open)
                {
                    await connection.OpenAsync();
                }

                using (var command = new MySqlCommand(query, connection, transaction))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }

                    LogHelper.Info($"[MySqlHelper.ExecuteNonQueryAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                    return await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"[MySqlHelper.ExecuteNonQueryAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
            finally
            {
                // 只有自己创建的连接才负责关闭和释放
                if (shouldDisposeConnection && connection != null)
                {
                    connection.Dispose();
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">sql查询语句</param>
        /// <param name="parameters"></param>
        /// <param name="transaction">可选的事务对象，用于控制多个操作的事务一致性</param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public static async Task<DataTable> ExecuteQueryAsync(
            string connectionString, 
            string query, 
            IEnumerable<MySqlParameter> parameters = null,
            MySqlTransaction transaction = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            bool shouldDisposeConnection = false;
            MySqlConnection connection = transaction?.Connection;

            try
            {
                if (connection == null)
                {
                    connection = new MySqlConnection(connectionString);
                    await connection.OpenAsync();
                    shouldDisposeConnection = true;
                }
                else if (connection.State != ConnectionState.Open)
                {
                    await connection.OpenAsync();
                }

                using (var command = new MySqlCommand(query, connection, transaction))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }
                    Console.WriteLine($"[MySqlHelper.ExecuteQueryAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                    LogHelper.Info($"[MySqlHelper.ExecuteQueryAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"[MySqlHelper.ExecuteQueryAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
            finally
            {
                if (shouldDisposeConnection && connection != null)
                {
                    connection.Dispose();
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回指定类型
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">sql查询语句</param>
        /// <param name="parameters"></param>
        /// <param name="transaction">可选的事务对象，用于控制多个操作的事务一致性</param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public static async Task<T> ExecuteScalarAsync<T>(
            string connectionString,
            string query,
            IEnumerable<MySqlParameter> parameters = null,
            MySqlTransaction transaction = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            bool shouldDisposeConnection = false;
            MySqlConnection connection = transaction?.Connection;

            try
            {
                if (connection == null)
                {
                    connection = new MySqlConnection(connectionString);
                    await connection.OpenAsync();
                    shouldDisposeConnection = true;
                }
                else if (connection.State != ConnectionState.Open)
                {
                    await connection.OpenAsync();
                }

                using (var command = new MySqlCommand(query, connection, transaction))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }

                    var result = await command.ExecuteScalarAsync();

                    LogHelper.Info(
                        $"[MySqlHelper.ExecuteScalarAsync]:{query}, Parameters:{FormatParameters(parameters)}");

                    return SafeCast<T>(result);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[MySqlHelper.ExecuteScalarAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
            finally
            {
                if (shouldDisposeConnection && connection != null)
                {
                    connection.Dispose();
                }
            }
        }
        /// <summary>
        /// 类型转换
        /// </summary>
        /// <param name="value"></param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private static T SafeCast<T>(object value)
        {
            if (value == null || value is DBNull)
            {
                return default;
            }

            // 直接匹配目标类型
            if (value is T typedValue)
            {
                return typedValue;
            }

            Type targetType = typeof(T);

            // 特别处理数值类型（如 int、long 等）
            if (Type.GetTypeCode(targetType) is TypeCode typeCode &&
                (typeCode is TypeCode.Int32 or TypeCode.Int64 or TypeCode.Decimal or TypeCode.Double) &&
                value is IConvertible convertible)
            {
                try
                {
                    return (T)Convert.ChangeType(convertible, targetType);
                }
                catch
                {
                    return default;
                }
            }

            // 默认尝试转换
            try
            {
                return (T)Convert.ChangeType(value, targetType);
            }
            catch
            {
                return default;
            }
        }


        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <returns>MySqlTransaction 对象</returns>
        public static async Task<MySqlTransaction> BeginTransactionAsync(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            var connection = new MySqlConnection(connectionString);
            await connection.OpenAsync();
            return connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="transaction">MySqlTransaction 对象</param>
        public static void CommitTransaction(MySqlTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException(nameof(transaction));
            }

            transaction.Commit();
            transaction.Connection.Close();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="transaction">MySqlTransaction 对象</param>
        public static void RollbackTransaction(MySqlTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException(nameof(transaction));
            }

            transaction.Rollback();
            transaction.Connection.Close();
        }

        /// <summary>
        /// 格式化参数日志
        /// </summary>
        private static string FormatParameters(IEnumerable<MySqlParameter> parameters)
        {
            if (parameters == null || !parameters.Any())
            {
                return "No Parameters";
            }

            return string.Join(", ", parameters.Select(p => $"{p.ParameterName}={p.Value}"));
        }
    }
}
